package com.angus.modules.front.mapper;

import com.angus.api.front.domain.FrontLoginInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface FrontLoginInfoMapper extends BaseMapper<FrontLoginInfo> {

    /**
     * 清空系统登录日志
     *
     * @return 结果
     */
    @Delete(" truncate table front_login_info")
    int cleanLoginInfo();

    /**
     * 根据日期和浏览器类型统计登录次数
     * @return List<BrowserStatsDTO> 包含日期、浏览器和计数的列表
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS stat_date, browser, COUNT(*) AS count " +
            "FROM front_login_info " +
            "WHERE browser IS NOT NULL AND browser <> '' " + // 排除空值
            "GROUP BY stat_date, browser " +
            "ORDER BY stat_date")
    List<Map<String, Object>> getBrowserStatsGroupedByDate();


    /**
     * 根据日期和终端类型统计登录次数
     * @return List<Map<String, Object>> 包含日期、终端和计数的列表
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS stat_date, terminal, COUNT(*) AS count " +
            "FROM front_login_info " +
            "WHERE terminal IS NOT NULL AND terminal <> '' " + // 排除空值和空字符串
            "GROUP BY stat_date, terminal " +
            "ORDER BY stat_date")
    List<Map<String, Object>> getTerminalStatsGroupedByDate();

    /**
     * 根据日期和IP 归属地统计登录次数
     * @return List<Map<String, Object>> 包含日期、IP 归属地和计数的列表
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS stat_date, ip_location, COUNT(*) AS count " +
            "FROM front_login_info " +
            "WHERE ip_location IS NOT NULL AND ip_location <> '' " + // 排除空值和空字符串
            "GROUP BY stat_date, ip_location " +
            "ORDER BY stat_date")
    List<Map<String, Object>> getIpLocationStatsGroupedByDate();
}
